//
//  DateBaseManager.m
//  ToYourHeartNews
//
//  Created by XJing on 16/1/29.
//  Copyright © 2016年 www.lanou3g.com 蓝鸥. All rights reserved.
//

#import "DateBaseManager.h"

@implementation DateBaseManager
+(DateBaseManager *)shareInstance
{
    //静态指针 创建数据库
    static DateBaseManager *dbManager = nil;
   
    if (!dbManager) {
        dbManager = [[DateBaseManager alloc]init];
    }
    return dbManager;
    
}
///打开数据库
-(void)openDB
{
    //数据库一般放在Document下 后缀:db/sqlite
    NSString *str = [[NSUserDefaults standardUserDefaults]objectForKey:@"name"];
    NSString *pathStr = [str stringByAppendingString:@"dataBase.db"];
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:pathStr];
    //语句  创建数据库文件
    //参数1: 数据库路径(转换成UTF8)
    //参数2: 数据库指针地址
    int result = sqlite3_open(path.UTF8String, &dbPoint);
    NSLog(@"打开数据库: %d, 路径: %@", result, path);
}
///关闭数据库
-(void)closeDB
{
    sqlite3_close(dbPoint);
}
///创建表
-(void)createTable
{
    //1.SQL语句
    NSString *createSQL = @"create table if not exists video (videoName text, videodescription text, mp4 text, videolength text, playCount text, replyCount tect, picture text, videoId integer PRIMARY KEY AUTOINCREMENT)";
    //2.执行语句
    //参数1: 数据库指针
    //参数2: sql语句
    //其他参数:NULL
    int result = sqlite3_exec(dbPoint, createSQL.UTF8String, NULL, NULL, NULL);
    //判断
    [self judgeResult:result message:@"创建表"];
}
///删除表
-(void)dropTable
{
    //1.SQL语句
    NSString *dropSQL = @"drop table video";
    //执行
    int result = sqlite3_exec(dbPoint, dropSQL.UTF8String, NULL, NULL, NULL);
    //判断
    [self judgeResult:result message:@"删除表"];
}
///增
-(void)insertVideo:(Videomodel *)m;
{
    //SQL
    NSString *insertSQL = [NSString stringWithFormat:@"insert into video (videoName, videodescription, mp4, videolength, playCount, replyCount, picture) values('%@', '%@', '%@', '%@', '%@', '%@', '%@')", m.title, m.description1, m.mp4_url, m.length.stringValue, m.playCount.stringValue, m.replyCount.stringValue, m.cover];
    //执行
    int result = sqlite3_exec(dbPoint, insertSQL.UTF8String, NULL, NULL, NULL);
    //判断
    [self judgeResult:result message:@"插入"];
}
///删
-(void)deleteVideoWithId:(NSInteger)videoId
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from video where videoId = '%ld'", videoId];
    int result = sqlite3_exec(dbPoint, deleteSQL.UTF8String, NULL, NULL, NULL);
    [self judgeResult:result message:@"删除"];
}
///改
-(void)updateVideo:(Videomodel *)newM withId:(NSInteger)VideoId
{
    NSString *updateSQL = [NSString stringWithFormat:@"update movie set videoName = '%@', videodescription = '%@', mp4 = '%@', videolength = '%@', playCount = '%@', replyCount = '%@', picture = '%@' where movieId = '%ld'", newM.title, newM.description1, newM.mp4_url, newM.length.stringValue, newM.playCount.stringValue, newM.replyCount.stringValue, newM.cover, VideoId];
    int result = sqlite3_exec(dbPoint, updateSQL.UTF8String, NULL, NULL, NULL);
    [self judgeResult:result message:@"更新数据"];
}
///查询
-(NSMutableArray *)select
{
    //查询后 数据处理过程
    //1.获取所有数据
    //2.先遍历行 得到每一行数据
    //3.再遍历当前行 每一列 按照对应关系保存在model中
    //4.model添加到数组 返回
    
    //SQL
    NSString *selectSQL = @"select *from video";
    //创建数据库替身
    sqlite3_stmt *stmt = nil;
    //SQL准备语句
    //把数据库对象/SQL语句/数据库替身 关联在一起
    int result = sqlite3_prepare_v2(dbPoint, selectSQL.UTF8String, -1, &stmt, nil);
    //初始化返回的数组
    NSMutableArray *arr = [NSMutableArray array];
    //判断准备是否成功
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        //遍历每一行数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //逐行获取每一行信息
            const unsigned char *vNAme = sqlite3_column_text(stmt, 0);
            const unsigned char *vDescription = sqlite3_column_text(stmt, 1);
            const unsigned char *Vmp4 = sqlite3_column_text(stmt, 2);
            const unsigned char *vLength = sqlite3_column_text(stmt, 3);
            const unsigned char *VplayCount = sqlite3_column_text(stmt, 4);
            const unsigned char *VreplyCount = sqlite3_column_text(stmt, 5);
            const unsigned char *Vpicture = sqlite3_column_text(stmt, 6);
            int vId = sqlite3_column_int(stmt, 7);
            //创建model对象
            Videomodel *m = [[Videomodel alloc]init];
            m.title = [NSString stringWithUTF8String:(const char *)vNAme];
            m.description1 = [NSString stringWithUTF8String:(const char *)vDescription];
            m.mp4_url = [NSString stringWithUTF8String:(const char *)Vmp4];
            NSString *lengthstr = [NSString stringWithUTF8String:(const char *)vLength];
            NSInteger lengthInt = lengthstr.integerValue;
            m.length = @(lengthInt);
            NSString *playstr = [NSString stringWithUTF8String:(const char *)VplayCount];
            NSInteger playInt = playstr.integerValue;
            m.playCount = @(playInt);
            NSString *replystr = [NSString stringWithUTF8String:(const char *)VreplyCount];
            NSInteger replyInt = replystr.integerValue;
            m.replyCount = @(replyInt);
            m.cover = [NSString stringWithUTF8String:(const char *)Vpicture];
            m.videoId = vId;
            
            [arr addObject:m];
        }
    }else{
        NSLog(@"失败: %d", result);
    }
    return arr;
}
///判断方法
-(void)judgeResult:(int)r message:(NSString *)mes
{
    if (r == SQLITE_OK) {
        NSLog(@"%@成功", mes);
    }else{
        NSLog(@"%@失败: %d", mes, r);
    }
}

@end
